﻿CREATE PROCEDURE [dbo].[proc_Department_Update_News]
	(
		@DName nvarchar(50),		
		@Sort int,
		@Id int,
		@ParentBMBH varchar(50)
	)
AS
			--Update Department Set
			--DName=@DName,			
			--Sort=@Sort
			--Where Id=@Id
			
			
			

declare @CompanyId int,@Parentid int 		

select @CompanyId=companyid from Department where id=@Id
if @ParentBMBH<>'0'
select @ParentId=id from Department where bmbh=@ParentBMBH
else
set @ParentId=0

--更新部门名称，排序，上级部门ID
Update Department Set DName=@DName,Sort=@Sort,ParentId=@ParentId Where Id=@Id

		
--更新公司所有部门的BMBH		
UPDATE Department SET bmbh = '' where companyid=@CompanyId	
	
declare @TempSca1 table(zid INT IDENTITY,did INT,bmbh VARCHAR(50) )

INSERT INTO @TempSca1(did,bmbh) SELECT c.id,c.Cid  
                                  FROM Company c,zzjg WHERE c.Id=zzjg.CompanyId and c.id=@CompanyId ORDER BY c.Id 
                       

DECLARE @i1 INT
SET @i1=1

;WITH List As(SELECT [Id]
      ,[DName]
      ,[ParentId]
	,0 As jb
,cast(t.bmbh+(CASE when len(ROW_NUMBER() over(order by sort))<2 THEN '0' ELSE '' END ) + Convert(varchar(2),ROW_NUMBER() over(order by sort)) As varchar(30)) as row
,sort
FROM [Department] d,@TempSca1 AS t
Where CompanyId=t.did And [ParentId]=0 AND t.zid=@i1
Union All
Select a.id,a.DName,a.ParentId,b.jb+1
,cast(b.row + (CASE when len(ROW_NUMBER() over(order by a.ParentId,a.sort,a.id))<2 THEN '0' ELSE '' END ) + Convert(varchar(2),ROW_NUMBER() over(order by a.ParentId,a.sort,a.id)) As varchar(30)) as row
,a.sort
From [Department] a,List b
Where a.[ParentId]=b.id 
)


UPDATE Department SET bmbh =ts.row FROM List ts WHERE Department.Id=ts.id and Department.companyid=@CompanyId	
			
						
			
		
	RETURN
